import sql # 自己写的库
import time,datetime
import tk_top # 自己写的分界面
import openpyxl
import re
import json

import tkinter
import tkinter as tk
from tkinter import ttk
from tkinter import messagebox
from tkinter import scrolledtext
from tkinter import *

# 界面抬头
main=tkinter.Tk()
main.title('xx教育孩子成长记录问卷表')
main.geometry('520x310+300+210')

# 任课教师
x_courseTeacher=10
y_courseTeacher=10
label_courseTeacher=Label(main,text='任课教师：')
label_courseTeacher.place(x=x_courseTeacher,y=y_courseTeacher)
cmb_courseTeacher=ttk.Combobox(main,height=10,width=15)
cmb_courseTeacher.place(x=x_courseTeacher+65,y=y_courseTeacher)
cmb_courseTeacher.set('--请选择--')
tuple_teacherMessage=sql.getTeacherMessage() # 从数据库获取教师名字 (('序号','教师','所有时间段'))
lst_teacherName=[]
for eachTeacher in tuple_teacherMessage:
	lst_teacherName.append(eachTeacher[1])
cmb_courseTeacher['value']=lst_teacherName # sql

# 上课课程
x_courseName=220
y_courseName=10
label_courseName=Label(main,text='上课课程：')
label_courseName.place(x=x_courseName,y=y_courseName)
cmb_courseLevel=ttk.Combobox(main,height=10,width=10)
cmb_courseLevel.place(x=x_courseName+65,y=y_courseName)
cmb_courseLevel.set('--请选择--')
cmb_courseLevel['value']=('启蒙初阶','启蒙进阶','启蒙高阶','机器人一阶','机器人二阶','机器人三阶','机器人四阶','机器人五阶','机器人六阶')
cmb_courseName=ttk.Combobox(main,height=10,width=16)
cmb_courseName.place(x=x_courseName+160,y=y_courseName)
cmb_courseName.set('--请选择--')
def showCourseName(event):
	cmb_courseName.set('--请选择--')
	courseLevel=cmb_courseLevel.get() # 从界面获取课程阶段
	tuple_courseMessage=sql.getCourseMessage_courseLevel(courseLevel) # 从数据库获取教师名字 (('序号','教师','所有时间段','结构','知识点'))
	lst_courseName=[]
	for eachCourse in tuple_courseMessage:
		lst_courseName.append(eachCourse[1])
	cmb_courseName['value']=lst_courseName # sql
'''根据课程阶段筛选课程'''
cmb_courseLevel.bind('<<ComboboxSelected>>',showCourseName)

# 是否代课
def showProxyTeacher():
	global label_proxyTeacher,cmb_proxyTeacher
	x_proxyTeacher=220
	y_proxyTeacher=40
	label_proxyTeacher=Label(main,text='代课老师：')
	label_proxyTeacher.place(x=x_proxyTeacher,y=y_proxyTeacher)
	cmb_proxyTeacher=ttk.Combobox(main,height=10,width=15)
	cmb_proxyTeacher.place(x=x_proxyTeacher+65,y=y_proxyTeacher)
	cmb_proxyTeacher.set('--请选择--')
	tuple_teacherMessage=sql.getTeacherMessage() # 从数据库获取教师名字 (('序号','教师','所有时间段'))
	lst_teacherName=[]
	for eachTeacher in tuple_teacherMessage:
		lst_teacherName.append(eachTeacher[1])
	cmb_proxyTeacher['value']=lst_teacherName # sql
'''选择是，显示代课老师选择项'''
def destroyProxyTeacher():
	try:
		label_proxyTeacher.destroy()
		cmb_proxyTeacher.destroy()
	except NameError:
		pass
	except Exception as e:
		print(e)
'''选择否，删除代课老师选择项'''
x_TFproxyTeacher=10
y_TFproxyTeacher=40
label_TFproxyTeacher=Label(main,text='是否代课：')
label_TFproxyTeacher.place(x=x_TFproxyTeacher,y=y_TFproxyTeacher)
radioButton_TFproxyTeacher=IntVar()
radioButton_TFproxyTeacher.set(2)
TFproxyTeacher1=Radiobutton(main,text="是",variable=radioButton_TFproxyTeacher,value=1,command=lambda:showProxyTeacher())
TFproxyTeacher1.place(x=x_TFproxyTeacher+60,y=y_TFproxyTeacher-2)
TFproxyTeacher2=Radiobutton(main,text="否",variable=radioButton_TFproxyTeacher,value=2,command=lambda:destroyProxyTeacher())
TFproxyTeacher2.place(x=x_TFproxyTeacher+115,y=y_TFproxyTeacher-2)

# 时间段
x_courseTime=10
y_courseTime=70
label_courseTime=Label(main,text='  时间段 ：')
label_courseTime.place(x=x_courseTime,y=y_courseTime)
cmb_courseTime=ttk.Combobox(main,height=10,width=15)
cmb_courseTime.place(x=x_courseTime+65,y=y_courseTime)
cmb_courseTime.set('--请选择--')
def showCourseTime(event):
	cmb_courseTime.set('--请选择--') # 更换任课老师后还原成初始值
	for eachStudent in table_studentFinshed.get_children(): # 删除界面表格信息
		table_studentFinshed.delete(eachStudent)
	courseTeacher=cmb_courseTeacher.get() # 从界面获取任课教师姓名
	tuple_teacherMessage=sql.getTeacherMessage() # 从数据库获取教师名字 (('序号','教师','所有时间段'))
	for eachTeacher in tuple_teacherMessage:
		if eachTeacher[1] == courseTeacher:
			courseTime=eachTeacher[2]
			cmb_courseTime['value']=courseTime.split('\n') # sql
			break
'''根据选择任课教师而筛选时间段'''
cmb_courseTeacher.bind('<<ComboboxSelected>>',showCourseTime)

# 上课日期
x_courseDate=220
y_courseDate=70
label_courseDate=Label(main,text='上课日期：')
label_courseDate.place(x=x_courseDate,y=y_courseDate)
entry_courseDate_year=Entry(main,width=5)
entry_courseDate_year.place(x=x_courseDate+65,y=y_courseDate)
label_courseDate_year=Label(main,text='年')
label_courseDate_year.place(x=x_courseDate+100,y=y_courseDate)
entry_courseDate_month=Entry(main,width=6)
entry_courseDate_month.place(x=x_courseDate+120,y=y_courseDate)
label_courseDate_month=Label(main,text='月')
label_courseDate_month.place(x=x_courseDate+155,y=y_courseDate)
entry_courseDate_day=Entry(main,width=6)
entry_courseDate_day.place(x=x_courseDate+175,y=y_courseDate)
label_courseDate_day=Label(main,text='日')
label_courseDate_day.place(x=x_courseDate+210,y=y_courseDate)
lst_week=['周一','周二','周三','周四','周五','周六','周日']
def update_weekFromCourseDate():
	year=entry_courseDate_year.get()
	month=entry_courseDate_month.get()
	day=entry_courseDate_day.get()
	if not '' in [year,month,day]:
		try:
			year,month,day=int(year),int(month),int(day)
			courseWeek=lst_week[datetime.date(year,month,day).weekday()]
			label_courseDate_date=Label(main,text=courseWeek,fg='blue')
			label_courseDate_date.place(x=x_courseDate+240,y=y_courseDate)
		except:
			pass
	main.after(1000,update_weekFromCourseDate)
'''根据填写的日期显示星期'''
update_weekFromCourseDate()

# 学生课评完成情况表
x_studentFinshed=10
y_studentFinshed=100
table_studentFinshed_column=['序号','学生','考勤','课程','上次保存记录']
table_studentFinshed=ttk.Treeview(
	master=main,
	columns=table_studentFinshed_column,
	height=7,
	show='headings',
	style='Treeview'
)
table_studentFinshed.column('序号',width=30)
table_studentFinshed.column('学生',width=90)
table_studentFinshed.column('考勤',width=90)
table_studentFinshed.column('课程',width=140)
table_studentFinshed.column('上次保存记录',width=150)
for i in range(len(table_studentFinshed_column)):
	table_studentFinshed.column(table_studentFinshed_column[i],anchor='c')
	table_studentFinshed.heading(table_studentFinshed_column[i],text=table_studentFinshed_column[i])
table_studentFinshed.place(x=x_studentFinshed,y=y_studentFinshed)
def showStudent(event):
	for eachStudent in table_studentFinshed.get_children(): # 删除界面表格信息
		table_studentFinshed.delete(eachStudent)

	courseTeacher=cmb_courseTeacher.get() # 从界面获取任课教师姓名
	courseTime=cmb_courseTime.get() # 从界面获取时间段
	tuple_studentMessage=sql.getStudentMessage(courseTeacher,courseTime) # 从数据库获取学生信息 (('序号','学生姓名','时间段','任课教师'))
	lst_studentName=[]
	i=1
	for eachStudenMessage in tuple_studentMessage:
		lst_studentName.append([str(i),eachStudenMessage[1]])
		i=i+1
	lst_studentName.append(['last','双击更新'])
	for eachStudentName in lst_studentName:
		table_studentFinshed_values=eachStudentName+['','',''] # sql
		table_studentFinshed.insert('','end',value=table_studentFinshed_values)

	courseTime=cmb_courseTime.get()
	entry_courseDate_year.delete(0,END)
	entry_courseDate_month.delete(0,END)
	entry_courseDate_day.delete(0,END)
	for i in range(-1,6):
		if lst_week[(datetime.datetime.now() - datetime.timedelta(days=i)).date().weekday()] in courseTime:
			weekDate=datetime.datetime.now() - datetime.timedelta(days=i)
			entry_courseDate_year.insert(0,weekDate.year)
			entry_courseDate_month.insert(0,weekDate.month)
			entry_courseDate_day.insert(0,weekDate.day)
			break
'''
清除界面表格信息，添加新信息；
根据时间段自动填写日期
'''
cmb_courseTime.bind('<<ComboboxSelected>>',showStudent)
def showTest(event):
	# 获取界面表格选择的学生信息
	tableValue=table_studentFinshed.item(table_studentFinshed.selection(),option='values')
	if tableValue == ('last', '双击更新', '', '', ''):
		for eachStudent in table_studentFinshed.get_children():
			eachTableValue=table_studentFinshed.item(eachStudent)['values']
			studentName=eachTableValue[1]
			appriseLog=sql.getAppriseLog_studentName(studentName)
			if appriseLog != ():
				appriseLog_datetime=appriseLog[-1][1]
				appriseLog_courseName=appriseLog[-1][4]
				appriseLog_attendanceStatus=appriseLog[-1][7]
				eachTableValue[2]=appriseLog_attendanceStatus
				eachTableValue[3]=appriseLog_courseName
				eachTableValue[4]=appriseLog_datetime
				table_studentFinshed.item(eachStudent,values=eachTableValue)
		return '更新完毕'
	else:
		try:
			studentName=tableValue[1]
		except IndexError:
			return False

	# 获取界面基本信息
	courseTeacher=cmb_courseTeacher.get() # 获取任课老师
	courseName=cmb_courseName.get() # 获取课程
	if courseName in ['--请选择--']:
		messagebox.showinfo('未选择提示','请选择上课课程')
		return '未选择课程'
	try:
		proxyTeacher=cmb_proxyTeacher.get() # 获取代课老师
	except:
		proxyTeacher='无'
	courseTime=cmb_courseTime.get() # 获取时间段
	year=entry_courseDate_year.get() # 获取日期，年
	month=entry_courseDate_month.get() # 获取日期，月
	day=entry_courseDate_day.get() # 获取日期，日
	if '' in [year,month,day]:# 判断是否填写
		messagebox.showinfo('未填写提示','请填写上课日期')
		return '未填写日期'
	year,month,day=int(year),int(month),int(day)
	courseWeek=lst_week[datetime.date(year,month,day).weekday()]
	if not courseWeek in courseTime:
		yesno=messagebox.askyesno('错误提示','上课周期与时间段不符，是否继续？')
		if yesno == False:
			return False
	today=datetime.date.today() # 今天
	beforday=datetime.date(year,month,day) # 之前上课日期
	apartday=today.__sub__(beforday).days # 今天 减去 之前上课日期
	if apartday >= 7:# 如果大于7天确认是否操作
		yesno=messagebox.askyesno('确认操作', '离上课日期超过7天，是否继续？')
		if yesno == False:
			return False
	courseDate=f'{str(year)}年{str(month)}月{str(day)}日'

	lst_mainMessage=[studentName,courseTeacher,courseName,proxyTeacher,courseDate,courseWeek]
	tk_top.studentApprise(lst_mainMessage)
'''问卷调查'''
table_studentFinshed.bind('<Double-1>',showTest)

# 生成课评
def countApprisseLog(lst_eightAbility):
	appriseScore=0
	for i in range(0,max(lst_eightAbility)+1):
		appriseScore=appriseScore+lst_eightAbility.count(i)/len(lst_eightAbility)/((i+1)+(i)*0.25)
	if appriseScore >= 0.9:
		appriseScore='优秀'
	elif appriseScore >= 0.8:
		appriseScore='优良'
	elif appriseScore >= 0.7:
		appriseScore='良好'
	elif appriseScore >= 0.6:
		appriseScore='平常'
	else:
		appriseScore='有待提高'
	return appriseScore
'''八大能力评分公式'''
def saveApprise():
	tableSelected=table_studentFinshed.selection()

	dict_allquestion={}
	for eachSelected in tableSelected:
		tableValue=table_studentFinshed.item(eachSelected,option='values')
		studentName=tableValue[1]
		courseName=tableValue[3]
		if courseName in ['']:
			messagebox.showinfo('提示','未查找到课程，请更新')
			return False

		# 确定选项位置用
		if dict_allquestion == {}:
			for eachQuestion in sql.getAllQuestion():
				lst_option=[]
				for i in range(6,15):
					if not eachQuestion[i] in ['','\n',None]:
						lst_option.append(eachQuestion[i])
				dict_allquestion[eachQuestion[0]]=lst_option

		# 统计结构及知识点数量用
		courseMessage=sql.getCourseMessage_courseName(courseName)[0]
		lst_structure=courseMessage[3].split('|')
		lst_knowledge=courseMessage[4].split('|')

		# 获取日志
		appriseLog=sql.getAppriseLog_studentName_courseName(studentName,courseName)
		lst_questionLog=appriseLog[0][8]
		lst_questionLog=re.sub('\'','"',lst_questionLog)
		lst_questionLog=re.sub('\n','',lst_questionLog)
		lst_questionLog=json.loads(lst_questionLog)

		# 处理评分和表现
		lst_focus=[]
		lst_selfConfident=[]
		lst_work=[]
		lst_deliver=[]
		lst_logic=[]
		lst_comprehension=[]
		lst_mood=[]
		lst_solve=[]
		str_goodDetail=''
		str_advice=''
		for eachQuestionLog in lst_questionLog:
			# 获取选项位置
			if eachQuestionLog['题型'] in ['单选题'] and eachQuestionLog['选择'] != ['']:
				# print(eachQuestionLog['能力归类'],eachQuestionLog['题型'],dict_allquestion[eachQuestionLog['总序号']].index(eachQuestionLog['选择'][0]),eachQuestionLog['细节描述'])
				if eachQuestionLog['能力归类'] == '专注力':
					lst_focus.append(dict_allquestion[eachQuestionLog['总序号']].index(eachQuestionLog['选择'][0]))
				elif eachQuestionLog['能力归类'] == '自信心':
					lst_selfConfident.append(dict_allquestion[eachQuestionLog['总序号']].index(eachQuestionLog['选择'][0]))
				elif eachQuestionLog['能力归类'] == '动手能力':
					lst_work.append(dict_allquestion[eachQuestionLog['总序号']].index(eachQuestionLog['选择'][0]))
				elif eachQuestionLog['能力归类'] == '语言表达':
					lst_deliver.append(dict_allquestion[eachQuestionLog['总序号']].index(eachQuestionLog['选择'][0]))
				elif eachQuestionLog['能力归类'] == '逻辑思维':
					lst_logic.append(dict_allquestion[eachQuestionLog['总序号']].index(eachQuestionLog['选择'][0]))
				elif eachQuestionLog['能力归类'] == '理解能力':
					lst_comprehension.append(dict_allquestion[eachQuestionLog['总序号']].index(eachQuestionLog['选择'][0]))
				elif eachQuestionLog['能力归类'] == '情绪管理':
					lst_mood.append(dict_allquestion[eachQuestionLog['总序号']].index(eachQuestionLog['选择'][0]))
				elif eachQuestionLog['能力归类'] == '解决问题能力':
					lst_solve.append(dict_allquestion[eachQuestionLog['总序号']].index(eachQuestionLog['选择'][0]))

				# 细节
				if dict_allquestion[eachQuestionLog['总序号']].index(eachQuestionLog['选择'][0]) == 0 and not eachQuestionLog['细节描述'] in ['','\n',None]:
					str_goodDetail=str_goodDetail+'细节：'+eachQuestionLog['题目']+'\n选项：'+eachQuestionLog['选择'][0]+'\n细节描述：'+eachQuestionLog['细节描述']+'\n\n'
				else:
					if not eachQuestionLog['细节描述'] in ['','\n',None]:
						str_advice=str_advice+'细节：'+eachQuestionLog['题目']+'\n选项：'+eachQuestionLog['选择'][0]+'\n细节描述：'+eachQuestionLog['细节描述']+'\n\n'

			elif eachQuestionLog['题型'] in ['多选题']:
				if eachQuestionLog['总序号'] in [404]:
					leftKnowledge=len(lst_knowledge)-len([i for i in eachQuestionLog['选择'] if i != ''])
					# print(eachQuestionLog['能力归类'],eachQuestionLog['题型'],leftKnowledge,eachQuestionLog['细节描述'])
					if eachQuestionLog['能力归类'] == '专注力':
						lst_focus.append(leftKnowledge)
					elif eachQuestionLog['能力归类'] == '自信心':
						lst_selfConfident.append(leftKnowledge)
					elif eachQuestionLog['能力归类'] == '动手能力':
						lst_selfConfident.append(leftKnowledge)
					elif eachQuestionLog['能力归类'] == '语言表达':
						lst_selfConfident.append(leftKnowledge)
					elif eachQuestionLog['能力归类'] == '逻辑思维':
						lst_selfConfident.append(leftKnowledge)
					elif eachQuestionLog['能力归类'] == '理解能力':
						lst_selfConfident.append(leftKnowledge)
					elif eachQuestionLog['能力归类'] == '情绪管理':
						lst_selfConfident.append(leftKnowledge)
					elif eachQuestionLog['能力归类'] == '解决问题能力':
						lst_selfConfident.append(leftKnowledge)

					if leftKnowledge == 0 and not eachQuestionLog['细节描述'] in ['','\n',None]:
						str_goodDetail=str_goodDetail+'细节：'+eachQuestionLog['题目']+'\n选项：'+'，'.join([i for i in eachQuestionLog['选择'] if i != ''])+'\n细节描述：'+eachQuestionLog['细节描述']+'\n\n'
					else:
						if not eachQuestionLog['细节描述'] in ['','\n',None]:
							str_advice=str_advice+'细节：'+eachQuestionLog['题目']+'\n选项：'+'，'.join([i for i in eachQuestionLog['选择'] if i != ''])+'\n细节描述：'+eachQuestionLog['细节描述']+'\n\n'

				elif eachQuestionLog['总序号'] in [402,501,604] and [i for i in eachQuestionLog['选择'] if i != ''] != []:
					leftStructure=len(lst_structure)-len([i for i in eachQuestionLog['选择'] if i != ''])
					# print(eachQuestionLog['能力归类'],eachQuestionLog['题型'],leftStructure,eachQuestionLog['细节描述'])
					if eachQuestionLog['能力归类'] == '专注力':
						lst_selfConfident.append(leftStructure)
					elif eachQuestionLog['能力归类'] == '自信心':
						lst_selfConfident.append(leftStructure)
					elif eachQuestionLog['能力归类'] == '动手能力':
						lst_selfConfident.append(leftStructure)
					elif eachQuestionLog['能力归类'] == '语言表达':
						lst_selfConfident.append(leftStructure)
					elif eachQuestionLog['能力归类'] == '逻辑思维':
						lst_selfConfident.append(leftStructure)
					elif eachQuestionLog['能力归类'] == '理解能力':
						lst_selfConfident.append(leftStructure)
					elif eachQuestionLog['能力归类'] == '情绪管理':
						lst_selfConfident.append(leftStructure)
					elif eachQuestionLog['能力归类'] == '解决问题能力':
						lst_selfConfident.append(leftStructure)

					if leftStructure == 0 and not eachQuestionLog['细节描述'] in ['','\n',None]:
						str_goodDetail=str_goodDetail+'细节：'+eachQuestionLog['题目']+'\n选项：'+'，'.join([i for i in eachQuestionLog['选择'] if i != ''])+'\n细节描述：'+eachQuestionLog['细节描述']+'\n\n'
					else:
						if not eachQuestionLog['细节描述'] in ['','\n',None]:
							str_advice=str_advice+'细节：'+eachQuestionLog['题目']+'\n选项：'+'，'.join([i for i in eachQuestionLog['选择'] if i != ''])+'\n细节描述：'+eachQuestionLog['细节描述']+'\n\n'

				else:
					if not eachQuestionLog['细节描述'] in ['','\n',None]:
						str_advice=str_advice+'细节：'+eachQuestionLog['题目']+'\n选项：'+'，'.join([i for i in eachQuestionLog['选择'] if i != ''])+'\n细节描述：'+eachQuestionLog['细节描述']+'\n\n'

			else:
				if not eachQuestionLog['细节描述'] in ['','\n',None]:
					str_advice=str_advice+'细节：'+eachQuestionLog['题目']+'\n选项：'+eachQuestionLog['选择']+'\n细节描述：'+eachQuestionLog['细节描述']+'\n\n'

		appriseScore_focus=countApprisseLog(lst_focus)
		appriseScore_selfConfident=countApprisseLog(lst_selfConfident)
		appriseScore_work=countApprisseLog(lst_work)
		appriseScore_deliver=countApprisseLog(lst_deliver)
		appriseScore_logic=countApprisseLog(lst_logic)
		appriseScore_comprehension=countApprisseLog(lst_comprehension)
		appriseScore_mood=countApprisseLog(lst_mood)
		appriseScore_solve=countApprisseLog(lst_solve)

		# 获取日期
		year=entry_courseDate_year.get()
		month=entry_courseDate_month.get()
		day=entry_courseDate_day.get()
		year,month,day=int(year),int(month),int(day)
		xlsx_date=re.sub('-','',str(datetime.date(year,month,day)))
		xlsx_weekday=lst_week[datetime.date(year,month,day).weekday()]
		
		# 打开表格并填写基本信息
		wb_appriseLog=openpyxl.load_workbook('成长记录呈现形式.xlsx')
		ws_appriseLog=wb_appriseLog.active
		ws_appriseLog.cell(2,2).value=studentName
		ws_appriseLog.cell(2,4).value=courseName
		ws_appriseLog.cell(2,6).value=str(datetime.date(year,month,day))
		ws_appriseLog.cell(3,5).value=appriseScore_focus
		ws_appriseLog.cell(4,5).value=appriseScore_selfConfident
		ws_appriseLog.cell(5,5).value=appriseScore_work
		ws_appriseLog.cell(6,5).value=appriseScore_deliver
		ws_appriseLog.cell(7,5).value=appriseScore_logic
		ws_appriseLog.cell(8,5).value=appriseScore_comprehension
		ws_appriseLog.cell(9,5).value=appriseScore_mood
		ws_appriseLog.cell(10,5).value=appriseScore_solve
		ws_appriseLog.cell(12,1).value=str_goodDetail
		ws_appriseLog.cell(14,1).value=str_advice
		try:
			wb_appriseLog.save(xlsx_date+xlsx_weekday+'-'+studentName+'-'+courseName+'.xlsx')
		except:
			messagebox.showinfo('提示','请关闭已打开的表格')

	messagebox.showinfo('提示','选择的学生课评已生成完毕')

button_saveApprise=Button(main,text='生成课评',bg='black',fg='white',command=saveApprise)
button_saveApprise.place(x=10,y=270)

# 界面循环
main.mainloop()